import sqlite3,os,time,hashlib

# 当前文件目录
dirpath = os.path.abspath('.')
DBNAME = os.path.join(dirpath,"api",'test.db') 

# 创建class
class Sqlite3:
    def __init__(self, db_path=DBNAME):
        self.db_path = db_path
        self.conn = sqlite3.connect(self.db_path)
        self.cursor = self.conn.cursor()

    # 创建数据库
    def create_db(self,sql):
        self.cursor.execute(sql)
        self.conn.commit()
    
    # 插入、修改、删除一条
    def sql_change_msg(self, sql):
        change_sql = self.cursor.execute(sql)
        self.conn.commit()
        return change_sql
    
    # 查询一条
    def sql_select_one(self, sql):
        self.cursor.execute(sql)
        select_res = self.cursor.fetchone()
        return select_res
    
    # 查询多条
    def sql_select_many(self, sql, count=None):
        self.cur.execute(sql)
        if count is None:
            select_res = self.cur.fetchall()
        else:
            select_res = self.cur.fetchmany(count)
        return select_res

    # 释放资源
    def release(self):
        self.conn.close()

    # 获取tables目录下的所有sql文件列表，并初始化数据库
    def init_db(self):
        # 获取目录下的所有sql文件
        sql_list = os.listdir(os.path.join(os.path.dirname(__file__), 'tables'))
        # 初始化数据库
        for sql_file in sql_list:
            # 读取sql文件内容
            with open(os.path.join(os.path.dirname(__file__), 'tables', sql_file), 'r', encoding='utf-8') as f:
                sql = f.read()
            self.create_db(sql)

if __name__ == '__main__':
    # 初始化数据库
    db = Sqlite3('test.db')
    # sqlite3.init_db()
    # 插入数据
    username = "root"
    password = 'root'
    # 密码MD5加密
    password = password.encode('utf-8')
    password = hashlib.md5(password).hexdigest()
    user = {
        "username":username,
        "password":password,
        "level":1, # 用户等级
        "create_time":int(time.time()), # 创建时间
        "status":1, # 0:禁用 1:启用
        "update_time":int(time.time()), # 上一次登录时间
        "token":'' # 用户token
    }
    # sql = f"insert into user(username,password,level,create_time,status,update_time,token) values('{username}','{password}',{user['level']},{user['create_time']},{user['status']},{user['update_time']},'{user['token']}')"
    # sqlite3.sql_change_msg(sql)
    
    # 查询数据
    # sql = "select * from user where id=1"
    # res = db.sql_select_one(sql)
    # print(res)